/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package persistencia;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import modelo.Partida;

/**
 *
 * @author alumneDAM
 */
public class PersiPartida {

    private ConexionBD conex = new ConexionBD();
    private Connection con;

    public ArrayList<Partida> cargarPartidas(String jugador) {
        ArrayList<Partida> partidas = new ArrayList();
        Partida p;

        try {
            this.conex.conexion();
            this.con = conex.getCon();

            Statement s = con.createStatement();
            ResultSet rs;

            rs = s.executeQuery("SELECT * FROM PARTIDA WHERE ESTADO LIKE 'JUGANDO' AND (ID_JUGADOR1 LIKE '" + jugador
                    + "' OR ID_JUGADOR2 LIKE '" + jugador + "')");

            while (rs.next()) {
                p = new Partida(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getInt(6), rs.getDate(7), rs.getInt(8), rs.getInt(9));
                partidas.add(p);
            }
            s.close();
            rs.close();
            conex.cerrarConexion();

        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        } finally {
            return partidas;
        }
    }

    public boolean guardarPartida(Partida partida) {
        try {
            this.conex.conexion();
            this.con = conex.getCon();
            PreparedStatement ps;
            String sql = "UPDATE partida SET  ID_JUGADOR1='" + partida.getId_jugador1()
                    + "', ID_JUGADOR2='" + partida.getId_jugador2()
                    + "', ESTADO='" + partida.getEstado()
                    + "', GANADOR='" + partida.getGanador()
                    + "', TURNO='" + partida.getTurno().getIdTurno()
                    + "', HORA=" + horaToSQL(partida.getHora())
                    + ", POSICIONJ1='" + partida.getPosicionJ1()
                    + "', POSICIONJ2='" + partida.getPosicionJ2()
                    + "' where ID_PARTIDA='" + partida.getId_partida() + "'";

            ps = con.prepareStatement(sql);
            ps.execute();
            ps.close();

            conex.cerrarConexion();
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
        return true;
    }

    public String horaToSQL(Date hora) {
        String salida;
        long horaPartida = hora.getTime();
        java.sql.Date sqlDate = new java.sql.Date(horaPartida);
        java.sql.Time sqlTime = new java.sql.Time(horaPartida);
        salida = " TO_DATE('" + sqlDate + " " + sqlTime
                + "','yyyy-mm-dd hh24:mi:ss') ";
        return salida;
    }

    public int crearPartida(Partida partida) {
        int id_partida = 0;

        try {
            this.conex.conexion();
            this.con = conex.getCon();
            PreparedStatement ps;
            String sql = "INSERT INTO partida VALUES ('"
                    + partida.getId_partida() + "','"
                    + partida.getId_jugador1() + "','"
                    + partida.getId_jugador2() + "','"
                    + partida.getEstado() + "','"
                    + partida.getGanador() + "','1',"
                    + horaToSQL(partida.getHora()) + ",'"
                    + partida.getPosicionJ1() + "','"
                    + partida.getPosicionJ2() + "')";

            System.out.println("SQL=" + sql);
            ps = con.prepareStatement(sql);
            ps.execute();
            ps.close();
            Statement s = con.createStatement();
            ResultSet rs;
            sql = "SELECT * FROM PARTIDA WHERE hora="
                    + horaToSQL(partida.getHora());
            System.out.println("sql=" + sql);
            rs = s.executeQuery(sql);

            while (rs.next()) {
                id_partida = rs.getInt(1);

            }
            rs.close();
            s.close();

            conex.cerrarConexion();
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
        return id_partida;
    }

    public Partida leerPartida(int id_partida) {
        Partida p = null;
        try {
            this.conex.conexion();
            this.con = conex.getCon();

            Statement s = con.createStatement();
            ResultSet rs;

            rs = s.executeQuery("SELECT * FROM PARTIDA WHERE id_partida='" + id_partida + "'");

            while (rs.next()) {
                p = new Partida(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4),
                        rs.getString(5), rs.getInt(6), rs.getTimestamp(7), rs.getInt(8), rs.getInt(9));
            }
            s.close();
            rs.close();
            conex.cerrarConexion();

        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        } finally {
            return p;
        }
    }

    public void guardarQuesito(int idPartida, String nickname, String color) {
        try {
            this.conex.conexion();
            this.con = conex.getCon();
            PreparedStatement ps;

            ps = con.prepareStatement("INSERT INTO porcion VALUES ('" + idPartida + "', '" + nickname + "', '" + color + "')");

            ps.execute();
            ps.close();
            conex.cerrarConexion();
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
    }

    public int recuperarNumeroQuesitos(int idPartida, String nickname) {
        int quesitos = 0;

        try {
            this.conex.conexion();
            this.con = conex.getCon();

            Statement s = con.createStatement();
            ResultSet rs;

            rs = s.executeQuery("SELECT COUNT(*) FROM porcion WHERE id_partida='" + idPartida + "' AND nickname='" + nickname + "'");

            while (rs.next()) {
                quesitos = rs.getInt(1);
            }
            s.close();
            rs.close();
            conex.cerrarConexion();

        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        } finally {
            return quesitos;
        }
    }

    public ArrayList<String> recuperarQuesitosJugador(int idPartida, String nickname) {
        ArrayList<String> quesitos = new ArrayList();

        try {
            this.conex.conexion();
            this.con = conex.getCon();

            Statement s = con.createStatement();
            ResultSet rs;

            rs = s.executeQuery("SELECT color FROM porcion WHERE id_partida='" + idPartida + "' AND nickname='" + nickname + "'");

            while (rs.next()) {
                quesitos.add(rs.getString(1));
            }
            s.close();
            rs.close();
            conex.cerrarConexion();

        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        } finally {
            return quesitos;
        }
    }
}
